#include "database.h"
#include "ui_database.h"
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QVariantList>
#include <QSqlError>
#include <QMessageBox>
#include <QDebug>

Database::Database(QWidget *parent) :
	QWidget(parent),
	ui(new Ui::Database)
{
	ui->setupUi(this);
	//打印支持的数据库驱动
	QStringList str = QSqlDatabase::drivers();
	qDebug() << str;
	//添加MySql数据库对象
	QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
	//连接数据库
	db.setHostName("rm-bp19kkjy1bne46g1cso.mysql.rds.aliyuncs.com");
	db.setUserName("bai");
	db.setPassword("bai188!@#");
	db.setDatabaseName("school");

	//打开数据库
	if(!db.open())
	{
		QMessageBox::warning(this,"数据库连接失败",db.lastError().text());
		return;
	}
	QSqlQuery query;
//	query.exec("select * from sc");
	//windows风格预处理
	/*
	query.prepare("insert into sc(sid,cid,score) values(?,?,?)");
	QVariantList sid_list;
	sid_list << 3 << 4 << 5;
	QVariantList cid_list;
	cid_list << 3 << 4 << 5;
	QVariantList score_list;
	score_list << 3 << 4 << 5;
	query.addBindValue(sid_list);
	query.addBindValue(cid_list);
	query.addBindValue(score_list);
	//执行预处理命令
	query.execBatch();
	*/
	//Oracle风格预处理,占位符:自定义名字
	/*
	query.prepare("insert into sc(sid,cid,score) values(:s,:c,:sc)");
	QVariantList sid_list;
	sid_list << 13 << 14 << 15;
	QVariantList cid_list;
	cid_list << 13 << 14 << 15;
	QVariantList score_list;
	score_list << 13 << 14 << 15;
	query.bindValue(":s",sid_list);
	query.bindValue(":c",cid_list);
	query.bindValue(":sc",score_list);
	query.execBatch();
	*/
	//查询数据库
	query.exec("select * from sc");
	while(query.next())
	{
		qDebug() << query.value(0).toInt()
				 << query.value(1).toInt()
				 << query.value("score").toInt();
	}
}

Database::~Database()
{
	delete ui;
}

void Database::on_pushButton_delete_clicked()
{
	QString id = ui->lineEdit->text();
	QString str = QString("delete from sc where sid = %1").arg(id);
	qDebug() << str;
	//通过database可以获取局部变量操作的数据库 database=db
	//开始一个事务
	QSqlDatabase::database().transaction();
	QSqlQuery query;
	query.exec(str);
	QSqlDatabase::database().rollback();
}

void Database::on_pushButton_confirm_clicked()
{
	QSqlDatabase::database().commit();
}

void Database::on_pushButton_cancel_clicked()
{
	QSqlDatabase::database().rollback();
}
